<?php
// +---------------------------------------------------------------------------+
// | This file is part of the KnowledgebasePublisher package                   |
// | KnowledgebasePublisher - web based knowledgebase publisher tool           |
// |                                                                           |
// | Author:  Evgeny Leontev <eleontev@gmail.com>                              |
// | Copyright (c) 2005 Evgeny Leontev                                         |
// |                                                                           |
// | For the full copyright and license information, please view the LICENSE   |
// | file that was distributed with this source code.                          |
// +---------------------------------------------------------------------------+

require_once 'core/app/DataToValueModel.php';
require_once 'core/common/CommonEntryModel.php';
require_once APP_MODULE_DIR . 'knowledgebase/category/inc/KBCategoryModel.php';

/*
attachment type/related type
1 - attachment
2 - inline file (link in document)
3 - exist as attachment and as inline
*/


class KBEntryModel extends CommonEntryModel
{
	var $tbl_pref_custom = 'kb_';
	var $tables = array('table'=>'entry', 'entry', 'category', 'entry_to_category', 
	                    'comment', 'rating', 'attachment_to_entry', 'related_to_entry');
	
	var $custom_tables = array('file_entry', 'role', 'feedback', 'user');
	var $member_rore_rule = 'kb_entry_to_member_role';
	
	
	function KBEntryModel() {
		parent::CommonEntryModel();
		$this->dv_manager = &new DataToValueModel();
	}	
	
	
	function getById($record_id) {
		$sql = "SELECT e.*, UNIX_TIMESTAMP(e.date_posted) AS ts
		FROM {$this->tbl->entry} e
		WHERE e.id = ?";
		
		//echo "<pre>"; print_r($sql); echo "</pre>";
		$result =& $this->db->Execute($sql, $record_id) or die(db_error($sql));
		return $result->FetchRow();
	}

	
	function getRecordsSql() {
		
		$sql = "
		SELECT 
			e.*,
			IF(e.body != '', e.active, 'not') AS active,
			CONCAT(SUBSTRING_INDEX(e.body,' ',20), '...') AS body,
            u.first_name AS first_name,
            u.middle_name AS middle_name,
            u.last_name AS last_name,
			cat.id AS category_id,
			#cat.name AS category_name,
			cat.private AS category_private,
			#cat.active AS category_active,
			r.votes AS votes,
			(r.rate/r.votes) AS rating,
			COUNT(DISTINCT c.id) AS comment_num,
			COUNT(DISTINCT a.attachment_id) AS attachment_num,
			UNIX_TIMESTAMP(e.date_posted) AS ts
			
		FROM 
			({$this->tbl->entry} e,
			{$this->tbl->category} cat,
			{$this->tbl->entry_to_category} e_to_cat,
            {$this->tbl->user} u)
		
		LEFT JOIN {$this->tbl->comment} c ON e.id = c.entry_id
		LEFT JOIN {$this->tbl->rating} r ON e.id = r.entry_id
		LEFT JOIN {$this->tbl->attachment_to_entry} a ON e.id = a.entry_id
		
		WHERE 1
			AND e.id = e_to_cat.entry_id
            AND u.id = e.author_id
			AND cat.id = e_to_cat.category_id
			AND {$this->sql_params}
		GROUP BY e.id
		{$this->sql_params_order}";
		
		//echo "<pre>"; print_r($sql); echo "</pre>";
		return $sql;
	}
	
	
	// for page by page
	function getCountRecordsSql() {
		$sql = "SELECT COUNT(DISTINCT e.id) 
		FROM 
			{$this->tbl->entry} e,
			{$this->tbl->category} cat,
			{$this->tbl->entry_to_category} e_to_cat		
		WHERE e.id = e_to_cat.entry_id
		AND cat.id = e_to_cat.category_id
		AND {$this->sql_params}";
		
		//echo "<pre>"; print_r($sql); echo "</pre>";
		return $sql;
	}
	
	
	function getSortRecords($category_id) {
		$sql = "SELECT e.id, e.title, e.sort_order 
		FROM 
			{$this->tbl->entry} e,
			{$this->tbl->entry_to_category} e_to_cat					
		WHERE 1
			AND e_to_cat.category_id = '%d'
			AND e.id = e_to_cat.entry_id
			AND e_to_cat.is_main = 1
		ORDER BY e.sort_order";
		
		$sql = sprintf($sql, $category_id);
		$result =& $this->db->Execute($sql) or die(db_error($sql));
		return $result->GetAssoc();	
	}
	
	
	// CATEGORIES // ---------------------------	
	
	function getCategoryRecords() {
		$this->cat = new KBCategoryModel();
		return $this->cat->getSelectRecords();
	}	
	
	
	
	// USERS QUESTIONS // ---------------------------	
	
	function getMemberQuestionById($id) {
		$sql = "SELECT * FROM {$this->tbl->feedback} WHERE id = %d";
		$result =& $this->db->Execute(sprintf($sql, $id)) or die(db_error($sql));
		return $result->FetchRow();
	}
	
	function setUserEntryPlaced($record_id) {
		$sql = "UPDATE {$this->tbl->feedback} SET placed = 1 WHERE id = '{$record_id}'";
		$this->db->Execute($sql) or die(db_error($sql));
	}
		
	
	// ATTACHMENT // -------------------------
	
	// get array with attachments filenames
	function &getAttachmentById($record_id, $attachment_type = '1,3') {
		
		$sql = "
		SELECT 
			f.id, 
			f.filename 
		FROM 
			{$this->tbl->file_entry} f, 
			{$this->tbl->attachment_to_entry} a
		WHERE 1
			AND a.entry_id IN ($record_id) 
			AND a.attachment_id = f.id
			AND a.attachment_type IN ($attachment_type)";
			
		$result =& $this->db->Execute($sql) or die(db_error($sql));
		return $result->GetAssoc();
	}
	
	
	function getAttachmentByIds($ids) {
		$sql = "SELECT f.id, f.filename 
		FROM {$this->tbl->file_entry} f
		WHERE f.id IN ($ids)";
		
		$result =& $this->db->Execute($sql) or die(db_error($sql));
		return $result->GetAssoc();
	}
	
	
	function getInlineAttachmentIds($str) {
	
		if(strpos($str, '[link:file') === false) {
			return array();
		}
		
		$search = "/\[link:file\|(\d+)\]/";		
		preg_match_all($search, $str, $match);
		return $match[1];
	}
	
	
	
	// RELATED // ---------------------	
	
	// get array with related titles
	function &getRelatedById($record_id, $related_type = '1,3') {
		
		$sql = "
		SELECT 
			e.id, 
			e.title 
		FROM 
			{$this->tbl->entry} e, 
			{$this->tbl->related_to_entry} r
		WHERE 1
			AND r.entry_id IN ($record_id) 
			AND r.related_entry_id = e.id
			AND r.related_type IN ($related_type)";
			
		$result =& $this->db->Execute($sql) or die(db_error($sql));
		return $result->GetAssoc();
	}
	
	
	function getRelatedByIds($ids) {
		$sql = "SELECT e.id, e.title 
		FROM {$this->tbl->entry} e
		WHERE e.id IN ($ids)";
			
		$result =& $this->db->Execute($sql) or die(db_error($sql));
		return $result->GetAssoc();
	}
	
	
	function getInlineRelatedIds($str) {
	
		if(strpos($str, '[link:article') === false) {
			return array();
		}
		
		$search = "/\[link:article\|(\d+)\]/";
		preg_match_all($search, $str, $match);
		return $match[1];
	}
	
	
	// ACTIONS // ---------------------
	
	function addEntry(&$obj) {	
		return $this->add($obj);
	}	
	
	
	function updateEntry(&$obj) {		
		$this->update($obj);
	}
	
	
	function saveEntryToCategory(&$obj, $record_id) {
		
		require_once 'eleontev/SQL/MultiInsert.php';
		
		foreach($obj->getCategory() as $k => $v) {
			$categories[] = array($v, 1);
		}
		
		foreach($obj->getCategoryMirror() as $k => $v) {
			$categories[] = array($v, 0);
		}		
		
		$sql = MultiInsert::get("INSERT IGNORE {$this->tbl->entry_to_category} (category_id, is_main, entry_id) 
		                         VALUES ?", $categories, array($record_id));
		
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	
	function saveAttachmentToEntry(&$obj, $record_id) {
		
		$inline_ids = $this->getInlineAttachmentIds($obj->get('body'));
		if(!$obj->getAttachment() && !$inline_ids) { return; }
		
		require_once 'eleontev/SQL/MultiInsert.php';
		
		$cat = $obj->getAttachment();
		foreach($cat as $k => $v) {
			$attachment[$v] = array(1, $v);
		}
		
		foreach($inline_ids as $k => $v) {
			$type = (isset($attachment[$v])) ? 3 : 2; // attachment type
			$attachment[$v] = array($type, $v);
		}		
		
		
		$sql = MultiInsert::get("INSERT {$this->tbl->attachment_to_entry} (attachment_type, attachment_id, entry_id) 
		                        VALUES ?", $attachment, array($record_id));
		
		//echo "<pre>"; print_r($inline_ids); echo "</pre>";
		//echo "<pre>"; print_r($sql); echo "</pre>";
		//exit;
		
		return $this->db->Execute($sql) or die(db_error($sql));
	}

	
	function saveRelatedToEntry(&$obj, $record_id) {
		
		$inline_ids = $this->getInlineRelatedIds($obj->get('body'));
		if(!$obj->getRelated() && !$inline_ids) { return; }
		
		require_once 'eleontev/SQL/MultiInsert.php';
		
		$cat = $obj->getRelated();
		foreach($cat as $k => $v) {
			$attachment[$v] = array(1, $v);
		}		
		
		foreach($inline_ids as $k => $v) {
			$type = (isset($attachment[$v])) ? 3 : 2; // attachment type
			$attachment[$v] = array($type, $v);
		}	
			
		$sql = MultiInsert::get("INSERT IGNORE {$this->tbl->related_to_entry} (related_type, related_entry_id, entry_id) 
		                        VALUES ?", $attachment, array($record_id));
								
		//echo "<pre>"; print_r($inline_ids); echo "</pre>";
		//echo "<pre>"; print_r($sql); echo "</pre>";
		//exit;								
		
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	
	//  return sort val and dod nessesary updates in sort_order field
	function sortManipulation(&$obj) {
		
		$category_entries = $this->getSortRecords(current($obj->getCategory()));

		// first one
		if(empty($category_entries)) {
			$obj->set('sort_order', 1);
		
		} else {
		
			$category_entries_ids = implode(',', array_keys($category_entries));
			
			// get sort value, we have it after some article
			// already means after
			if($obj->get('sort_order') != 'sort_begin' && 
			   $obj->get('sort_order') != 'sort_end') {
				
				$obj->set('sort_order', $category_entries[$obj->get('sort_order')]['sort_order']);
			}
		
			$sort = new TableSortOrder();
			$sort->table = $this->tbl->entry;
			$sort->name_field = 'title';
			$sort->sort_field = 'sort_order';		
			$sort->setMoreSql("id IN ({$category_entries_ids})",
			                  "id IN ({$category_entries_ids})");
							  
			$val = $sort->getDoSort($obj->get('sort_order'), $obj->get('id'));
			$obj->set('sort_order', $val);			
		}
	}	
		
	
	
	function save(&$obj) {
		
		$this->sortManipulation($obj);		
		
		if(!$this->isExtra('private')) {
			$obj->set('private', 0);
		}
		
		// insert
		if(!$obj->get('id')) {
			
			$id = $this->addEntry($obj);
			
			$this->saveEntryToCategory($obj, $id);
			$this->saveRelatedToEntry($obj, $id);
			$this->saveAttachmentToEntry($obj, $id);
			
			if($this->isExtra('private')) {
				if($obj->get('private')) {
					$this->saveMemberRoleToEntry($obj, $id);
				}
			}
		
		// update
		} else {
			
			$id = $obj->get('id');
			
			$this->updateEntry($obj);
			$this->deleteEntryToCategory($id);
			$this->saveEntryToCategory($obj, $id);
			
			$this->deleteRelatedToEntry($id);
			$this->saveRelatedToEntry($obj, $id);	
			
			$this->deleteAttachmentToEntry($id);
			$this->saveAttachmentToEntry($obj, $id);			
			
			if($this->isExtra('private')) {
				$this->deleteMemberRoleToEntry($id);
				if($obj->get('private')) {
					$this->saveMemberRoleToEntry($obj, $id);
				}					
			}
		}
		
		return $id;
	}
	
	
	// PRIV // ------------------------------	
	
	// if check priv is different for model so reassign 
	function checkPriv(&$priv, $action, $record_id, $popup = false) {

		$priv->setCustomAction('question', 'insert');
		
		if($priv->getPrivId() == 5) { //  untrusted author
			$sql = "SELECT 1 FROM {$this->tbl->table} 
			WHERE id = '{$record_id}' 
			AND author_id = {$priv->user_id}
			AND updater_id = {$priv->user_id}";
		} else {
			$sql = "SELECT 1 FROM {$this->tbl->table}  
			WHERE id = '{$record_id}' AND author_id = {$priv->user_id}";
		}		
		
		$priv->setOwnSql($sql);
		$priv->check($action);
		
		// set sql to select own records
		if($popup) { $priv->setOwnParam(1); } 
		else       { $priv->setOwnParam($this->getOwnParams($priv)); }
		
		$this->setSqlParams('AND ' . $priv->getOwnParam());
	}
	
	function getOwnParams($priv) {
		return sprintf("author_id=%d", $priv->user_id);
	}
	
	
	// DELETE RELATED // --------------------- 
	
	function deleteEntries($record_id) {
		$sql = "DELETE FROM {$this->tbl->entry} WHERE id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	function deleteRating($record_id) {
		$sql = "DELETE FROM {$this->tbl->rating} WHERE entry_id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	function deleteComments($record_id) {
		$sql = "DELETE FROM {$this->tbl->comment} WHERE entry_id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	function deleteEntryToCategory($record_id) {
		$sql = "DELETE FROM {$this->tbl->entry_to_category} WHERE entry_id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	// delete by entry_id from related if any
	// so we do not have any related to this entry
	function deleteRelatedToEntry($record_id) {
		$sql = "DELETE FROM {$this->tbl->related_to_entry} WHERE entry_id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}	
	
	function deleteAttachmentToEntry($record_id) {
		$sql = "DELETE FROM {$this->tbl->attachment_to_entry} WHERE entry_id IN ({$record_id})";
		return $this->db->Execute($sql) or die(db_error($sql));
	}
	
	
	function delete($record_id) {
		
		// convert to string 1,2,3... to use in IN()
		$record_id = $this->idToString($record_id);
		
		$this->deleteEntries($record_id);
		$this->deleteRating($record_id);
		$this->deleteComments($record_id);
		$this->deleteEntryToCategory($record_id);
		$this->deleteRelatedToEntry($record_id);
		$this->deleteAttachmentToEntry($record_id);
		
		if($this->isExtra('private')) {
			$this->deleteMemberRoleToEntry($record_id);
		}
	}
}
?>